﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace POSSchemas
{
    public partial class Form1 : Form
    {
        string cadenaConexionBase = "Data Source={0};Initial Catalog=master;User ID={1};Password={2}";
        string cadenaConexionBase2 = "Data Source={0};Initial Catalog={1};User ID={2};Password={3}";
        string cadenaConexion = "";
        string servidor = "";
        string usuario = "";
        string password = "";
        string basedatos = "";
        string tabla = "";

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            groupBox2.Enabled = false;
            groupBox1.Text = "Servidor [ sin conexión ] ";
            button2.Enabled = false;
            //cboTabla.Enabled = false;
            configuracionInicialCuadroDialogo();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            conecta();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            inicializa();
        }

        private void conecta()
        {
            string sqlCadena = "";            

            servidor = txtServidor.Text.Trim();
            usuario = txtUsuario.Text.Trim();
            password = txtPassword.Text.Trim();

            cadenaConexion = string.Format(cadenaConexionBase, servidor,
                usuario, password);
            bdsql bd = new bdsql(cadenaConexion);
            try
            {
                bd.bdConecta();
                DataSet ds;

                sqlCadena = "SELECT name as 'base de datos' FROM master..sysdatabases ";
                ds = bd.bdConsulta(sqlCadena, "bases");
                cboBD.DataSource = ds.Tables["bases"].DefaultView;

                //cboTabla.Enabled = true;
                

                groupBox1.Enabled = false;
                groupBox1.Text = "Servidor [ conectado ] ";
                button2.Enabled = true;
                groupBox2.Enabled = true;
                cboTabla.Enabled = false;
                button3.Enabled = false;
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                bd.bdCierra();
            }
        }

        private void inicializa()
        {
            servidor = "";
            usuario = "";
            password = "";
            basedatos = "";
            tabla = "";
            txtServidor.Text = "";
            txtUsuario.Text = "";
            txtPassword.Text = "";
            cboBD.Text = "";
            cboTabla.Text = "";
            groupBox1.Text = "Servidor [ sin conexión ] ";
            groupBox1.Enabled = true;
            groupBox2.Enabled = false;
            button2.Enabled = false;
            cboTabla.Enabled = false;
            button3.Enabled = false;
            grdDatos.DataSource = null;
            grdDatos.DataBind();
            grdDatos2.DataSource = null;
            grdDatos2.DataBind();
            grdDatos3.DataSource = null;
            grdDatos3.DataBind();
            
        }

        private void configuracionInicialCuadroDialogo()
        {
            openFileDialog1.FileOk += new CancelEventHandler(openFileDialog1_FileOk);

            //Definimos los filtros de archivos a permitir, en este caso imagenes
            openFileDialog1.Filter = "Sql Scripts (*.sql)|*.sql|Archivos de texto (*.txt)|*.txt ";
            ///Establece que filtro se mostrará por deceto en este caso, 3=jpg
            openFileDialog1.FilterIndex = 3;
            ///Esto aparece en el Nombre del archivo a seleccionar, se puede quitar no es fundamental
            openFileDialog1.FileName = "script1.sql";

            //El titulo de la Ventana....
            openFileDialog1.Title = "Generador Script SQL CyA";

            //El directorio que por deceto habrirá, para cada contrapleca del Path colocar \\ C:\\Fotitos\\Wizard y así sucesivamente
            openFileDialog1.InitialDirectory = "c:\\";
        }





        private void cboBD_Leave(object sender, EventArgs e)
        {
            
            string sqlCadena = "";
            string sqlCadenaBase = "use {0} SELECT table_name as 'tabla' FROM Information_Schema.Tables where Table_Type = 'BASE TABLE'";            
            cadenaConexion = string.Format(cadenaConexionBase, servidor,
            usuario, password);
            bdsql bd = new bdsql(cadenaConexion);

            try
            {
                bd.bdConecta();
                DataSet ds;

                sqlCadena = string.Format(sqlCadenaBase, cboBD.Text.Trim());
                basedatos = cboBD.Text.Trim();
                ds = bd.bdConsulta(sqlCadena, "tablas");
                cboTabla.DataSource = ds.Tables["tablas"].DefaultView;
                cboTabla.Enabled = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                bd.bdCierra();
            }
        }

        private void cboTabla_Leave(object sender, EventArgs e)
        {
            tabla = cboTabla.Text.Trim();
            button3.Enabled = true;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            GeneraScript();
        }

        private void GeneraScript()
        {
            if (txtRutaScript.Text.Trim() == string.Empty)
            {
                MessageBox.Show("No existe ruta o nombre de arhivo inválido para guardar script");
                return;
            }
            

            string sqlCadena = "";            
            string sqlCadenaBase = "use {0} SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM Information_Schema.Columns WHERE TABLE_NAME = '{1}'";
            string sqlBase2;
            if(chkTop100.Checked == true )
                sqlBase2 = " ;use {0} SELECT top 100 * FROM {1}";
            else
                 sqlBase2 = " ;use {0} SELECT * FROM {1}";

            cadenaConexion = string.Format(cadenaConexionBase, servidor,
            usuario, password);
            bdsql bd = new bdsql(cadenaConexion);

            try
            {
                bd.bdConecta();
                DataSet ds;
                sqlCadena = string.Format(sqlCadenaBase, basedatos,tabla);
                sqlCadena += string.Format(sqlBase2, basedatos, tabla);


                ds = bd.bdConsulta(sqlCadena, "estructura");
                grdDatos.DataSource = ds.Tables[0].DefaultView;
                grdDatos.DataBind();

                grdDatos2.DataSource = ds.Tables[1].DefaultView;
                grdDatos2.DataBind();


                /* Este bloque es para generar el script Insert en SQL Server
                 * Inicio
                */

                DataTable tablaPaso = new DataTable("tablaPaso");
                DataColumn columPaso = new DataColumn("Query", System.Type.GetType("System.String"));
                columPaso.MaxLength = 5000;
                tablaPaso.Columns.Add(columPaso);

                //DataColumn[] Keys = new DataColumn[1];
                //Keys[0] = columPaso;
                //tablaPaso.PrimaryKey = Keys;


                string strSentencia = "INSERT INTO {0} ( ";
                strSentencia = string.Format(strSentencia, tabla);
                string strFinal = strSentencia;
                string strSentenciaInsertStore = strFinal;
                int contador = 0;
                int contador2 = 0;
                DataRow drFinal;

                foreach (DataRow dr in ds.Tables[1].Rows)
                {
                    foreach (DataRow dr2 in ds.Tables[0].Rows)
                    {
                        if (contador <= ds.Tables[0].Rows.Count )
                        {
                            if (contador == ds.Tables[0].Rows.Count - 1)
                            {
                                strFinal += dr2["COLUMN_NAME"].ToString() + ")";
                                if(contador2 == 0) //Esto es para el script de SP para insertar
                                    strSentenciaInsertStore += dr2["COLUMN_NAME"].ToString() + ")";
                            }
                            else
                            {
                                strFinal += dr2["COLUMN_NAME"].ToString() + ",";
                                if (contador2 == 0) //Esto es para el script de SP para insertar
                                    strSentenciaInsertStore += dr2["COLUMN_NAME"].ToString() + ",";
                            }
                        }

                        contador++;                        
                    }

                    contador2++; //Esto es para el script de SP para insertar
                    contador =0;
                    strFinal += " VALUES (";
                    
                    foreach (DataRow dr2 in ds.Tables[0].Rows)
                    {
                        if (contador <= ds.Tables[0].Rows.Count)
                        {
                            if (contador == ds.Tables[0].Rows.Count - 1)
                            {
                                if (dr2["DATA_TYPE"].ToString() == "varchar" ||
                                    dr2["DATA_TYPE"].ToString() == "datetime" ||
                                    dr2["DATA_TYPE"].ToString() == "smalldatetime" ||
                                    dr2["DATA_TYPE"].ToString() == "char" ||
                                    dr2["DATA_TYPE"].ToString() == "nvarchar" ||
                                    dr2["DATA_TYPE"].ToString() == "text" ||
                                    dr2["DATA_TYPE"].ToString() == "ntext" ||
                                    dr2["DATA_TYPE"].ToString() == "uniqueidentifier" ||
                                    dr2["DATA_TYPE"].ToString() == "time" ||
                                    dr2["DATA_TYPE"].ToString() == "timestamp" ||
                                    dr2["DATA_TYPE"].ToString() == "nchar")
                                {
                                    if (dr[dr2["COLUMN_NAME"].ToString()] == DBNull.Value)
                                    {
                                        if (dr2["IS_NULLABLE"].ToString() == "YES")
                                        {
                                            strFinal += "null )";
                                        }
                                        else
                                        {
                                            switch (dr2["DATA_TYPE"].ToString())
                                            {
                                                case "datetime":
                                                case "smalldatetime":
                                                    strFinal += "getdate() )";
                                                    break;
                                                case "time":
                                                case "timestamp":
                                                    strFinal += "CONVERT(VARCHAR(8),GETDATE(),108) )";
                                                    break;
                                                default:
                                                    strFinal += "' ' )";
                                                    break;
                                            }
                                        }
                                    }
                                    else
                                    {
                                        strFinal += "'" + dr[dr2["COLUMN_NAME"].ToString()].ToString() + "')";
                                    }
                                }
                                else
                                {
                                    if (dr[dr2["COLUMN_NAME"].ToString()] == DBNull.Value)
                                    {
                                        if (dr2["IS_NULLABLE"].ToString() == "YES")                                        
                                            strFinal += "null )";
                                       
                                        else                                        
                                            strFinal += "0 )";
                                        
                                    }
                                    else
                                    {
                                        if (dr2["DATA_TYPE"].ToString() == "bit")
                                        {
                                            if(dr[dr2["COLUMN_NAME"].ToString()].ToString() == "False")
                                                strFinal += "0)";
                                            else
                                                strFinal += "1)";
                                        }
                                        else
                                        {
                                            strFinal += dr[dr2["COLUMN_NAME"].ToString()].ToString() + ")";
                                        }
                                    }
                                }
                            }
                            else
                            {
                                if (dr2["DATA_TYPE"].ToString() == "varchar" ||
                                    dr2["DATA_TYPE"].ToString() == "datetime" ||
                                    dr2["DATA_TYPE"].ToString() == "smalldatetime" ||
                                    dr2["DATA_TYPE"].ToString() == "char" ||
                                    dr2["DATA_TYPE"].ToString() == "nvarchar" ||
                                    dr2["DATA_TYPE"].ToString() == "text" ||
                                    dr2["DATA_TYPE"].ToString() == "ntext" ||
                                    dr2["DATA_TYPE"].ToString() == "uniqueidentifier" ||
                                    dr2["DATA_TYPE"].ToString() == "time" ||
                                    dr2["DATA_TYPE"].ToString() == "timestamp" ||
                                    dr2["DATA_TYPE"].ToString() == "nchar")
                                {
                                    if (dr[dr2["COLUMN_NAME"].ToString()] == DBNull.Value)
                                    {
                                        if (dr2["IS_NULLABLE"].ToString() == "YES")
                                        {
                                            strFinal += "null ,";
                                        }
                                        else
                                        {
                                            switch (dr2["DATA_TYPE"].ToString())
                                            {
                                                case "datetime":
                                                case "smalldatetime":
                                                    strFinal += "getdate(),";
                                                    break;
                                                case "time":
                                                case "timestamp":
                                                    strFinal += "CONVERT(VARCHAR(8),GETDATE(),108) ,";
                                                    break;
                                                default:
                                                    strFinal += "' ',";
                                                    break;
                                            }
                                        }
                                    }
                                    else
                                    {
                                        strFinal += "'" + dr[dr2["COLUMN_NAME"].ToString()].ToString() + "',";
                                    }
                                }
                                else
                                {
                                    if (dr[dr2["COLUMN_NAME"].ToString()] == DBNull.Value)
                                    {
                                        if (dr2["IS_NULLABLE"].ToString() == "YES")                                        
                                            strFinal += "null ,";                                       
                                        else                                       
                                            strFinal += "0,";                                       
                                    }
                                    else
                                    {
                                        if (dr2["DATA_TYPE"].ToString() == "bit")
                                        {
                                            if (dr[dr2["COLUMN_NAME"].ToString()].ToString() == "False")
                                                strFinal += "0,";
                                            else
                                                strFinal += "1,";
                                        }
                                        else
                                        {
                                            strFinal += dr[dr2["COLUMN_NAME"].ToString()].ToString() + ",";
                                        }
                                    }
                                }
                                    
                            }
                        }
                        contador++;
                    }

                    drFinal = tablaPaso.NewRow();
                    drFinal["Query"] = strFinal;
                    tablaPaso.Rows.Add(drFinal);
                    strFinal = strSentencia;
                    contador = 0;
                }
                drFinal = null;
                /* Fin del bloque
                 */ 

                //columPaso = new DataColumn("Valor", System.Type.GetType("System.String"));
                //columPaso.MaxLength = 150;
                //tablaPaso.Columns.Add(columPaso);

                //DataRow dr;
                //foreach (DataColumn dc in ds.Tables[1].Columns)
                //{
                //    dr = tablaPaso.NewRow();
                //    dr["Nombre"] = dc.ColumnName;
                //    dr["Valor"] = "na";
                //    tablaPaso.Rows.Add(dr);
                //}



                grdDatos3.DataSource = tablaPaso.DefaultView;
                grdDatos3.DataBind();

                string nombreArchivo = txtRutaScript.Text.Substring(0, txtRutaScript.Text.Length - 4);

                //Esto es para generar codigo C# para uso de la tabla en programación
                if (chkGeneraCodigo.Checked == true)
                {

                    DataTable tablaPasoCodigo = new DataTable("tablaPasoCodigo");
                    DataColumn columPasoCodigo = new DataColumn("LineaCodigo", System.Type.GetType("System.String"));
                    columPasoCodigo.MaxLength = 5000;
                    tablaPasoCodigo.Columns.Add(columPasoCodigo);

                    strSentencia = "public string ";                
                    strFinal = strSentencia;
                    
                    
                    DataRow drCodigo;
                    foreach (DataRow dr2 in ds.Tables[0].Rows)
                    {
                        strFinal += dr2["COLUMN_NAME"].ToString() + " { get; set; }";
                        drCodigo = tablaPasoCodigo.NewRow();
                        drCodigo["LineaCodigo"] = strFinal;
                        tablaPasoCodigo.Rows.Add(drCodigo);
                        strFinal = strSentencia;
                    }

                    using (System.IO.StreamWriter file = new System.IO.StreamWriter(nombreArchivo + ".cs"))
                    {
                        file.WriteLine("using System;");
                        file.WriteLine("using System.Collections.Generic;");
                        file.WriteLine("using System.Text;");
                        file.WriteLine(" ");
                        file.WriteLine("namespace CambiarNameSpace");
                        file.WriteLine("{");
                        file.WriteLine(" ");
                        file.WriteLine("    public class " + tabla);
                        file.WriteLine("    {");
                        foreach (DataRow dr in tablaPasoCodigo.Rows)
                        {
                            file.WriteLine("        " + dr["LineaCodigo"].ToString());
                        }
                        file.WriteLine("    }");
                        file.WriteLine("}");
                    }

                }

                
                if (chkSPs.Checked == true)
                {                    
                    strSentenciaInsertStore += " VALUES (";
                    contador = 0;
                    string nombreStore = "SPIns" + tabla;
                    using (System.IO.StreamWriter file = new System.IO.StreamWriter(nombreArchivo + "SpIns.sql"))
                    {
                        file.WriteLine("USE " + basedatos);
                        file.WriteLine("GO");
                        file.WriteLine("SET ANSI_NULLS ON");
                        file.WriteLine("GO");
                        file.WriteLine("SET QUOTED_IDENTIFIER ON");
                        file.WriteLine(" ");
                        file.WriteLine("GO");
                        file.WriteLine(" ");
                        file.WriteLine(" IF EXISTS (SELECT * FROM dbo.sysobjects ");
                        file.WriteLine(" WHERE id = object_id(N'[dbo].[" + nombreStore +"]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
                        file.WriteLine(" DROP PROCEDURE [dbo].[" + nombreStore +"]");
                        file.WriteLine(" GO");
                        file.WriteLine(" ");
                        file.WriteLine("CREATE procedure [dbo].[" + nombreStore +"]");
                        file.WriteLine(" ");

                        string caracterFinal = "";
                        foreach (DataRow dr2 in ds.Tables[0].Rows)
                        {
                            if (contador <= ds.Tables[0].Rows.Count)
                            {
                                if (contador == ds.Tables[0].Rows.Count - 1)
                                {
                                    strSentenciaInsertStore += "@" + dr2["COLUMN_NAME"].ToString() + ")";
                                    caracterFinal = " ";                                    
                                }
                                else
                                {
                                    strSentenciaInsertStore += "@" + dr2["COLUMN_NAME"].ToString() + ",";
                                    caracterFinal = ",";
                                }

                                if (dr2["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
                                    file.WriteLine("@" + dr2["COLUMN_NAME"].ToString() +
                                        " " + dr2["DATA_TYPE"].ToString() + " (" +
                                        dr2["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")" + caracterFinal);
                                else
                                    file.WriteLine("@" + dr2["COLUMN_NAME"].ToString() +
                                        " " + dr2["DATA_TYPE"].ToString() + caracterFinal );                                
                                
                            }

                            contador++;
                        }

                        file.WriteLine(" ");
                        file.WriteLine("AS");
                        file.WriteLine("BEGIN");
                        file.WriteLine("	" + strSentenciaInsertStore);
                        file.WriteLine("END");
                        file.WriteLine("GO");

                    }

                    string strSentenciaUpdate = "UPDATE " + tabla + " SET ";
                    nombreStore = "SPUpd" + tabla;
                    contador = 0;
                    using (System.IO.StreamWriter file = new System.IO.StreamWriter(nombreArchivo + "SpUpd.sql"))
                    {
                        file.WriteLine("USE " + basedatos);
                        file.WriteLine("GO");
                        file.WriteLine("SET ANSI_NULLS ON");
                        file.WriteLine("GO");
                        file.WriteLine("SET QUOTED_IDENTIFIER ON");
                        file.WriteLine(" ");
                        file.WriteLine("GO");
                        file.WriteLine(" ");
                        file.WriteLine(" IF EXISTS (SELECT * FROM dbo.sysobjects ");
                        file.WriteLine(" WHERE id = object_id(N'[dbo].[" + nombreStore + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
                        file.WriteLine(" DROP PROCEDURE [dbo].[" + nombreStore + "]");
                        file.WriteLine(" GO");
                        file.WriteLine(" ");
                        file.WriteLine("CREATE procedure [dbo].[" + nombreStore + "]");
                        file.WriteLine(" ");

                        string strSentenciaUpdate2 = "";
                        string caracterFinal = "";
                        foreach (DataRow dr2 in ds.Tables[0].Rows)
                        {
                            if (contador <= ds.Tables[0].Rows.Count)
                            {
                                if (contador == ds.Tables[0].Rows.Count - 1)
                                {                                    
                                    strSentenciaUpdate2 += dr2["COLUMN_NAME"].ToString() + " = " + "@" + dr2["COLUMN_NAME"].ToString();
                                    caracterFinal = " ";
                                    
                                }
                                else
                                {
                                    strSentenciaUpdate2 += dr2["COLUMN_NAME"].ToString() + " = " + "@" + dr2["COLUMN_NAME"].ToString() + ",";
                                    caracterFinal = ",";
                                }

                                if (dr2["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
                                    file.WriteLine("@" + dr2["COLUMN_NAME"].ToString() +
                                        " " + dr2["DATA_TYPE"].ToString() + " (" +
                                        dr2["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")" + caracterFinal);
                                else
                                    file.WriteLine("@" + dr2["COLUMN_NAME"].ToString() +
                                        " " + dr2["DATA_TYPE"].ToString() + caracterFinal);

                            }

                            contador++;
                        }
                        strSentenciaUpdate += strSentenciaUpdate2;
                        file.WriteLine(" ");
                        file.WriteLine("AS");
                        file.WriteLine("BEGIN");
                        file.WriteLine("	" + strSentenciaUpdate);
                        file.WriteLine("END");
                        file.WriteLine("GO");
                    }

                    string strSentenciaDelete = "DELETE " + tabla + " WHERE ";
                    nombreStore = "SPDlt" + tabla;
                    contador = 0;
                    using (System.IO.StreamWriter file = new System.IO.StreamWriter(nombreArchivo + "SpDlt.sql"))
                    {
                        file.WriteLine("USE " + basedatos);
                        file.WriteLine("GO");
                        file.WriteLine("SET ANSI_NULLS ON");
                        file.WriteLine("GO");
                        file.WriteLine("SET QUOTED_IDENTIFIER ON");
                        file.WriteLine(" ");
                        file.WriteLine("GO");
                        file.WriteLine(" ");
                        file.WriteLine(" IF EXISTS (SELECT * FROM dbo.sysobjects ");
                        file.WriteLine(" WHERE id = object_id(N'[dbo].[" + nombreStore + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
                        file.WriteLine(" DROP PROCEDURE [dbo].[" + nombreStore + "]");
                        file.WriteLine(" GO");
                        file.WriteLine(" ");
                        file.WriteLine("CREATE procedure [dbo].[" + nombreStore + "]");
                        file.WriteLine(" ");
                        if (ds.Tables[0].Rows[0]["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
                            file.WriteLine("@" + ds.Tables[0].Rows[0]["COLUMN_NAME"].ToString() +
                                " " + ds.Tables[0].Rows[0]["DATA_TYPE"].ToString() + " (" +
                                 ds.Tables[0].Rows[0]["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")");
                        else
                            file.WriteLine("@" + ds.Tables[0].Rows[0]["COLUMN_NAME"].ToString() +
                                " " + ds.Tables[0].Rows[0]["DATA_TYPE"].ToString());

                        strSentenciaDelete += ds.Tables[0].Rows[0]["COLUMN_NAME"].ToString() + " = @" + ds.Tables[0].Rows[0]["COLUMN_NAME"].ToString();

                        file.WriteLine(" ");
                        file.WriteLine("AS");
                        file.WriteLine("BEGIN");
                        file.WriteLine("	" + strSentenciaDelete);
                        file.WriteLine("END");
                        file.WriteLine("GO");
                    }

                }

                using (System.IO.StreamWriter file = new System.IO.StreamWriter (txtRutaScript.Text))
                {
                    foreach (DataRow dr in tablaPaso.Rows)
                    {
                        file.WriteLine(dr["Query"].ToString());                        
                    }
                }

               

                

                button3.Enabled = false;


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                bd.bdCierra();
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            openFileDialog1.ShowDialog();                                   
        }

        void openFileDialog1_FileOk(object sender, CancelEventArgs e)
        {
            txtRutaScript.Text = openFileDialog1.FileName;
        }

    }
}
